﻿using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;

using XianShang.Data;
using XianShang.Entity;
using XianShang.Common;
namespace XianShang.Data.SqlServer
{
    public partial class DataProvider : IDataProvider
    {
        /// <summary>
        /// 在換卡主表中插入一條新數據并返回自增ID值
        /// </summary>
        /// <param name="maInfo"></param>
        /// <returns></returns>
        public int AddNewMoveAccount(MoveAccountInfo maInfo)
        {
            DbParameter[] getPar ={
                                       DbHelper.MakeInParam("@area",(DbType)SqlDbType.TinyInt,3,maInfo.Area),
                                      DbHelper.MakeInParam("@banknum",(DbType)SqlDbType.NVarChar,50,maInfo.BankNum),
                                     DbHelper.MakeInParam("@reasons",(DbType)SqlDbType.NVarChar,50,maInfo.Reasons),
                                     DbHelper.MakeInParam("@name",(DbType)SqlDbType.NVarChar,50,maInfo.Name),
                                     DbHelper.MakeInParam("@oldaccount",(DbType)SqlDbType.NVarChar,200,maInfo.OldAccount),
                                     DbHelper.MakeInParam("@oldgameaccount",(DbType)SqlDbType.NVarChar,200,maInfo.OldGameAccount),
                                      DbHelper.MakeInParam("@contacttype",(DbType)SqlDbType.NVarChar,50,maInfo.ContactType),
                                     DbHelper.MakeInParam("@newaccount",(DbType)SqlDbType.NVarChar,200,maInfo.NewAccount),
                                     DbHelper.MakeInParam("@password",(DbType)SqlDbType.NVarChar,50,maInfo.Password),
                                     DbHelper.MakeInParam("@tel",(DbType)SqlDbType.NVarChar,30,maInfo.Tel),
                                     DbHelper.MakeInParam("@email",(DbType)SqlDbType.NVarChar,200,maInfo.Email),
                                     DbHelper.MakeInParam("@bbsaccount",(DbType)SqlDbType.NVarChar,200,maInfo.BbsAccount),
                                     DbHelper.MakeInParam("@content",(DbType)SqlDbType.NVarChar,500,maInfo.Content),
                                     DbHelper.MakeInParam("@status",(DbType)SqlDbType.TinyInt,3,maInfo.Status),
                                     DbHelper.MakeInParam("@ip",(DbType)SqlDbType.NVarChar,50,maInfo.IP),
                                     DbHelper.MakeInParam("@time",(DbType)SqlDbType.DateTime,DateTime.Now.ToString().Length,maInfo.Time)
                                 };          
            
            string commandText = "INSERT INTO [dbo].[XS_MoveAccount]";
            commandText += " ([Area],[Reasons],[OldAccount],[NewAccount],[OldGameAccount],[Password],[Name],[Tel],[BankNum],[contacttype],[Email] ,[BbsAccount],[Content],[Status],[IP],[Time])";
            commandText+="VALUES(@area,@reasons,@oldaccount,@newaccount,@oldgameaccount,@password,@name,@tel,@banknum,@contacttype,@email,@bbsaccount,@content,@status,@ip,@time)";

            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }

        /// <summary>
        /// 根據指定值查找轉移帳戶信息
        /// </summary>
        /// <param name="name"></param>
        /// <param name="tel"></param>
        /// <param name="oldAccount"></param>
        /// <param name="newAccount"></param>
        /// <returns></returns>
        public DataSet GetMoveAccount(string name, string tel, string oldAccount, string newAccount)
        {
            #region 根據條件處理參數
            List<string> arrCts = new List<string>();
            List<DbParameter> arrPar = new List<DbParameter>();

            if (!Utils.StrIsEmpty(name))
            {
                arrPar.Add(DbHelper.MakeInParam("@name", (DbType)SqlDbType.NVarChar, 50, name));
                arrCts.Add("[dbo].[XS_MoveAccount].[name]=@name");
            }
            if (!Utils.StrIsEmpty(tel))
            {
                arrPar.Add(DbHelper.MakeInParam("@tel", (DbType)SqlDbType.NVarChar, 50, tel));
                arrCts.Add("[dbo].[XS_MoveAccount].[tel]=@tel");
            }
            if (!Utils.StrIsEmpty(oldAccount))
            {
                arrPar.Add(DbHelper.MakeInParam("@oldaccount", (DbType)SqlDbType.NVarChar, 100, oldAccount));
                arrCts.Add("[dbo].[XS_MoveAccount].[oldaccount]=@oldaccount");
            }
            if (!Utils.StrIsEmpty(newAccount))
            {
                arrPar.Add(DbHelper.MakeInParam("@newaccount", (DbType)SqlDbType.NVarChar, 100, newAccount));
                arrCts.Add("[dbo].[XS_MoveAccount].[newaccount]=@newaccount");
            }
            arrPar.Add(DbHelper.MakeInParam("@status", (DbType)SqlDbType.TinyInt, 3, 4));
            arrCts.Add("[dbo].[XS_MoveAccount].[status]<>@status");
            string  conditions = null;

            DbParameter[] getPar = new DbParameter[arrCts.Count];
            for (int iCount = 0; iCount < arrCts.Count; ++iCount)
            {
                if (iCount == 0)
                {
                    conditions = "WHERE " + arrCts[iCount];
                }
                else
                {
                    conditions += " AND " + arrCts[iCount];
                }
                getPar[iCount] = arrPar[iCount];
            }
            
            #endregion     
 
            string commandText = string.Format("SELECT * FROM [dbo].[XS_MoveAccount] {0} ORDER BY [dbo].[XS_MoveAccount].[TIME] DESC", conditions);
            return DbHelper.ExecuteDataset(CommandType.Text,commandText,getPar);

        }

        /// <summary>
        /// 根據 name,tel,bbsaccount,status,newaccount,oldaccount,time取得信息
        /// </summary>
        /// <param name="ma"></param>
        /// <param name="SearchType">查找方式（1精確0模糊）</param>
        /// <returns></returns>
        public IDataReader GetMoveAccountInfo(MoveAccountInfo ma,int SearchType)
        {
            #region 根據條件處理參數
            List<string> arrCts = new List<string>();
            List<DbParameter> arrPar = new List<DbParameter>();
            
            if (!Utils.StrIsEmpty(ma.Name))
            {
                arrPar.Add(DbHelper.MakeInParam("@name", (DbType)SqlDbType.NVarChar, 50, ma.Name));
                arrCts.Add(SearchType == 1 ? "[dbo].[XS_MoveAccount].[name]=@name" : "[dbo].[XS_MoveAccount].[name] LIKE '%'+@name+'%'");
            }
            if (!Utils.StrIsEmpty(ma.Tel))
            {
                arrPar.Add(DbHelper.MakeInParam("@tel", (DbType)SqlDbType.NVarChar, 100, ma.Tel));
                arrCts.Add(SearchType == 1 ?"[dbo].[XS_MoveAccount].[tel]=@tel":"[dbo].[XS_MoveAccount].[tel] LIKE '%'+@tel+'%'");
            }
            if (!Utils.StrIsEmpty(ma.OldAccount))
            {
                arrPar.Add(DbHelper.MakeInParam("@oldaccount", (DbType)SqlDbType.NVarChar, 100, ma.OldAccount));
                arrCts.Add(SearchType == 1 ?"[dbo].[XS_MoveAccount].[oldaccount]=@oldaccount":"[dbo].[XS_MoveAccount].[oldaccount] LIKE '%'+@oldaccount+'%'");
            }
            if (!Utils.StrIsEmpty(ma.NewAccount))
            {
                arrPar.Add(DbHelper.MakeInParam("@newaccount", (DbType)SqlDbType.NVarChar, 100, ma.NewAccount));
                arrCts.Add(SearchType == 1 ? "[dbo].[XS_MoveAccount].[newaccount]=@newaccount" : "[dbo].[XS_MoveAccount].[newaccount] LIKE '%'+@newaccount+'%'");
            }
             if (!Utils.StrIsEmpty(ma.BbsAccount))
            {
                arrPar.Add(DbHelper.MakeInParam("@bbsaccount", (DbType)SqlDbType.NVarChar, 100, ma.BbsAccount));
                arrCts.Add(SearchType == 1 ? "[dbo].[XS_MoveAccount].[bbsaccount]=@bbsaccount" : "[dbo].[XS_MoveAccount].[bbsaccount] LIKE '%'+@bbsaccount+'%'");
            }
             if (!Utils.StrIsEmpty(ma.Time))
            {
                arrPar.Add(DbHelper.MakeInParam("@time", (DbType)SqlDbType.DateTime, ma.Time.Length, ma.Time));
                arrCts.Add(SearchType == 1 ? "DateDiff(DAY,[dbo].[XS_MoveAccount].[time],@time)=0" : "YEAR([dbo].[XS_MoveAccount].[time])=year(@time) AND MONTH([dbo].[XS_MoveAccount].[time])=MONTH(@time) AND DAY([dbo].[XS_MoveAccount].[time])=DAY(@time)");
            }
             if (!Utils.StrIsEmpty(ma.Area))
             {
                 arrPar.Add(DbHelper.MakeInParam("@area", (DbType)SqlDbType.TinyInt, 3, ma.Area));
                 arrCts.Add("[dbo].[XS_MoveAccount].[Area]=@area");
             }
             if (ma.Status >= 0)
             {
                 arrPar.Add(DbHelper.MakeInParam("@status", (DbType)SqlDbType.TinyInt, 3, ma.Status));
                 arrCts.Add("[dbo].[XS_MoveAccount].[status]=@status");
             }
             else
             {
                 arrPar.Add(DbHelper.MakeInParam("@status", (DbType)SqlDbType.TinyInt, 3, 4));
                 arrCts.Add("[dbo].[XS_MoveAccount].[status]<>@status");
             }

            string conditions = null;
            DbParameter[] getPar = new DbParameter[arrPar.Count];
            for (int iCount = 0; iCount < arrCts.Count; ++iCount)
            {
                if (iCount == 0)
                {
                    conditions = "WHERE " + arrCts[iCount];
                }
                else
                {
                    conditions += " AND " + arrCts[iCount];
                }
                getPar[iCount] = arrPar[iCount];
            }

            #endregion

            string commandText = string.Format("SELECT * FROM [dbo].[XS_MoveAccount] {0} ORDER BY [dbo].[XS_MoveAccount].[Time] DESC", conditions);
            return DbHelper.ExecuteReader(CommandType.Text, commandText, getPar);
        }

        /// <summary>
        /// 取得所有轉移信息
        /// </summary>
        /// <returns></returns>
        public IDataReader GetAllMoveAccountInfo(string area)
        {
            DbParameter getPar = DbHelper.MakeInParam("@area", (DbType)SqlDbType.TinyInt, 3, area);
            string commandText = "SELECT * FROM [dbo].[XS_MoveAccount] WHERE [dbo].[XS_MoveAccount].[area]=@area AND [dbo].[XS_MoveAccount].[status]<> 4  ORDER BY [dbo].[XS_MoveAccount].[Time] DESC ";
            return DbHelper.ExecuteReader(CommandType.Text,commandText,getPar);
        }
        /// <summary>
        /// 取得所有轉移信息
        /// </summary>
        /// <returns></returns>
        public IDataReader GetAllMoveAccountInfo()
        {
            string commandText = "SELECT * FROM [dbo].[XS_MoveAccount] WHERE [dbo].[XS_MoveAccount].[status]<> 4 ORDER BY [dbo].[XS_MoveAccount].[Time] DESC ";
            return DbHelper.ExecuteReader(CommandType.Text, commandText);
        }


        /// <summary>
        /// 更新Message ProcessIp Status ProcessTime四個字段
        /// </summary>
        /// <param name="cs"></param>
        /// <returns></returns>
        public int UpMoveAccountMessageProcessIpStatusProcessTime(MoveAccountInfo ma)
        {
            DbParameter[] getPar ={
                                     DbHelper.MakeInParam("@id",(DbType)SqlDbType.Int,int.MaxValue,ma.ID),
                                     DbHelper.MakeInParam("@message",(DbType)SqlDbType.NVarChar,500,ma.Message),
                                     DbHelper.MakeInParam("@processip",(DbType)SqlDbType.NVarChar,50,ma.ProcessIP),
                                     DbHelper.MakeInParam("@processtime",(DbType)SqlDbType.DateTime,ma.ProcessTime.Length,ma.ProcessTime),
                                     DbHelper.MakeInParam("@status",(DbType)SqlDbType.TinyInt,4,ma.Status)
                                 };
            string commandText = "UPDATE [dbo].[XS_MoveAccount]";
            commandText += "SET [Message] = @message,[Status] = @status,[ProcessIP] = @processip,[ProcessTime] = @processtime ";
            commandText += "WHERE [dbo].[XS_MoveAccount].[id]=@id";
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 更新 ProcessIp Status ProcessTime三個字段
        /// </summary>
        /// <param name="cs"></param>
        /// <returns></returns>
        public int UpMoveAccountStatusProcessTimeProcessIpById(MoveAccountInfo ma)
        {
            DbParameter[] getPar ={
                                     DbHelper.MakeInParam("@id",(DbType)SqlDbType.Int,int.MaxValue,ma.ID),
                                     DbHelper.MakeInParam("@processip",(DbType)SqlDbType.NVarChar,50,ma.ProcessIP),
                                     DbHelper.MakeInParam("@processtime",(DbType)SqlDbType.DateTime,ma.ProcessTime.Length,ma.ProcessTime),
                                     DbHelper.MakeInParam("@status",(DbType)SqlDbType.TinyInt,4,ma.Status)
                                 };
            string commandText = "UPDATE [dbo].[XS_MoveAccount]";
            commandText += "SET [Status] = @status,[ProcessIP] = @processip,[ProcessTime] = @processtime ";
            commandText += "WHERE [dbo].[XS_MoveAccount].[id]=@id";
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }

        /// <summary>
        /// 根據ID查找記錄
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataSet GetMoveAccountById(string id)
        {
            DbParameter getPar = DbHelper.MakeInParam("@id",(DbType)SqlDbType.Int,int.MaxValue,id);
            string commandText = "SELECT * FROM [dbo].[XS_MoveAccount] WHERE [dbo].[XS_MoveAccount].[ID]=@id";
            return DbHelper.ExecuteDataset(CommandType.Text,commandText,getPar);

        }

        /// <summary>
        /// 根據ID刪除記錄
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DelMoveAccountById(string id)
        {
            DbParameter getPar = DbHelper.MakeInParam("@id", (DbType)SqlDbType.Int, int.MaxValue, id);
            string commandText = "DELETE FROM [dbo].[XS_MoveAccount] WHERE [dbo].[XS_MoveAccount].[ID]=@id";
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
    }
}
